package com.oa.srms.apps.dao.student.impl;

import java.sql.SQLException;
import java.util.List;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;
import com.oa.srms.apps.constants.IDEDStates;
import com.oa.srms.apps.constants.IStudentStates;
import com.oa.srms.apps.dao.student.IStudentInfoDao;
import com.oa.srms.apps.entity.student.StudentDetailValue;
import com.oa.srms.apps.entity.student.StudentInfoValue;

public class StudentInfoDaoImpl extends HibernateDaoSupport implements IStudentInfoDao {

	@Autowired
	private List<StudentInfoValue> list;
	
	private int totalNumber;
	@Override
	public Integer addStudentInfo(StudentInfoValue studentInfoValue)
			throws Exception {
		if(studentInfoValue != null){
			this.getHibernateTemplate().save(studentInfoValue);
		}
		return studentInfoValue.getId();
	}

	@Override
	public void editStudentInfo(StudentInfoValue studentInfoValue)
			throws Exception {
		if(studentInfoValue != null){
			this.getHibernateTemplate().update(studentInfoValue);
		}
	}

	@Override
	public StudentInfoValue queryById(Integer id) throws Exception {
		return (StudentInfoValue) this.getHibernateTemplate().get(StudentInfoValue.class, id);
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<StudentInfoValue> queryStudentInfo(final String stuNo,
			final String stuName,  final Integer states, final StudentDetailValue studentDetail,
			final Integer rows, final Integer pageNo) throws Exception {
		String hqlStr = "SELECT siv FROM StudentInfoValue siv, StudentDetailValue sdv WHERE 1=1 AND siv.stuDetail.id = sdv.id ";
		if (stuNo != null) {
			hqlStr += " AND siv.stuNo = ? ";
		}
		if (stuName != null) {
			hqlStr += " AND siv.stuName = ? ";
		}
		if (states != null) {
			hqlStr += " AND siv.states = ? ";
		}
		if(studentDetail != null){
			if (studentDetail.getGender() != null) {
				hqlStr += " AND sdv.gender = ? ";
			}
			if (studentDetail.getMajorInfo() != null) {
				hqlStr += " AND sdv.majorInfo = ? ";
			}
			if (studentDetail.getGrade() != null) {
				hqlStr += " AND sdv.grade = ? ";
			}
			if (studentDetail.getClassNo() != null) {
				hqlStr += " AND sdv.classNo = ? ";
			}
		}
		hqlStr += " AND siv.states <> ?  ORDER BY siv.stuNo DESC";
		final String hql = hqlStr;
		list = this.getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query query = session.createQuery(hql);
				int index = -1;
				if (stuNo != null) {
					query.setString(++index, stuNo);
				}
				if (stuName != null) {
					query.setString(++index, stuName);
				}
				if (states != null) {
					query.setInteger(++index, states);
				}
				if(studentDetail != null){
					if (studentDetail.getGender() != null) {
						query.setString(++index, studentDetail.getGender());
					}
					if (studentDetail.getMajorInfo() != null) {
						query.setEntity(++index, studentDetail.getMajorInfo());
					}
					if (studentDetail.getGrade() != null) {
						query.setInteger(++index, studentDetail.getGrade());
					}
					if (studentDetail.getClassNo() != null) {
						query.setInteger(++index, studentDetail.getClassNo());
					}
				}
				query.setInteger(++index, IDEDStates.DELETE);
				query.setFirstResult(rows * (pageNo - 1));
				query.setMaxResults(rows);
				list = query.list();
				return list;
			}
		});
		return list;
	}

	@SuppressWarnings("unchecked")
	@Override
	public Integer getTotalPageNumber(final String stuNo, final String stuName, final Integer states, final StudentDetailValue studentDetail, Integer rows) {
		int totalPageNumber = 0;
		String hqlStr = "SELECT siv FROM StudentInfoValue siv, StudentDetailValue sdv WHERE 1=1 AND siv.stuDetail.id = sdv.id ";
		if (stuNo != null) {
			hqlStr += " AND siv.stuNo = ? ";
		}
		if (stuName != null) {
			hqlStr += " AND siv.stuName = ? ";
		}
		if (states != null) {
			hqlStr += " AND siv.states = ? ";
		}
		if(studentDetail != null){
			if (studentDetail.getGender() != null) {
				hqlStr += " AND sdv.gender = ? ";
			}
			if (studentDetail.getMajorInfo() != null) {
				hqlStr += " AND sdv.majorInfo = ? ";
			}
			if (studentDetail.getGrade() != null) {
				hqlStr += " AND sdv.grade = ? ";
			}
			if (studentDetail.getClassNo() != null) {
				hqlStr += " AND sdv.classNo = ? ";
			}
		}
		hqlStr += " AND siv.states <> ?  ORDER BY siv.stuNo";
		final String hql = hqlStr;
		list = this.getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query query = session.createQuery(hql);
				int index = -1;
				if (stuNo != null) {
					query.setString(++index, stuNo);
				}
				if (stuName != null) {
					query.setString(++index, stuName);
				}
				if (states != null) {
					query.setInteger(++index, states);
				}
				if(studentDetail != null){
					if (studentDetail.getGender() != null) {
						query.setString(++index, studentDetail.getGender());
					}
					if (studentDetail.getMajorInfo() != null) {
						query.setEntity(++index, studentDetail.getMajorInfo());
					}
					if (studentDetail.getGrade() != null) {
						query.setInteger(++index, studentDetail.getGrade());
					}
					if (studentDetail.getClassNo() != null) {
						query.setInteger(++index, studentDetail.getClassNo());
					}
				}
				query.setInteger(++index, IDEDStates.DELETE);
				list = query.list();
				return list;
			}
		});
		totalNumber = list.size();
		if (totalNumber <= rows) {
			totalPageNumber = 1;
		} else {
			if (totalNumber % rows == 0) {
				totalPageNumber = totalNumber / rows;
			} else {
				totalPageNumber = totalNumber / rows + 1;
			}
		}
		return totalPageNumber;
	}

	@Override
	public Integer getTotalNumber() {
		return totalNumber;
	}

	@SuppressWarnings("unchecked")
	@Override
	public StudentInfoValue queryByStuNo(String stuNo) throws Exception {
		String hqlStr = "SELECT siv FROM StudentInfoValue siv WHERE siv.stuNo = ? And siv.states <> -1";
		list = this.getHibernateTemplate().find(hqlStr, stuNo);
		if(list != null && list.size()!=0){
			return list.get(0);
		}
		return null;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<StudentInfoValue> queryStudentInfoForSendMail(
			final Integer studentState, final Integer mailState) throws Exception{
		String hqlStr = "SELECT siv FROM StudentInfoValue siv, MailInfoValue miv WHERE siv.mailInfo.mailId = miv.mailId  AND siv.states = ?  AND miv.states = ? ";
		final String hql = hqlStr;
		list = this.getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query query = session.createQuery(hql);
				if (studentState != null) {
					query.setInteger(0, studentState);
				}
				if (mailState != null) {
					query.setInteger(1, mailState);
				}
				list = query.list();
				return list;
			}
		});
		return list;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<StudentInfoValue> queryStudentInfo(Integer states)
			throws Exception {
		String hqlStr = "SELECT siv FROM StudentInfoValue siv WHERE siv.states = ?  AND siv.states <> -1";
		return this.getHibernateTemplate().find(hqlStr, states);
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<StudentInfoValue> queryStudentInfoAboveStates(final String stuNo,
			final String stuName, final Integer states, final StudentDetailValue studentDetail,
			final Integer rows, final Integer pageNo) throws Exception {
		String hqlStr = "SELECT siv FROM StudentInfoValue siv, StudentDetailValue sdv WHERE 1=1 AND siv.stuDetail.id = sdv.id ";
		if (stuNo != null) {
			hqlStr += " AND siv.stuNo = ? ";
		}
		if (stuName != null) {
			hqlStr += " AND siv.stuName = ? ";
		}
		if (states != null) {
			hqlStr += " AND siv.states >= ? ";
		}
		if(studentDetail != null){
			if (studentDetail.getGender() != null) {
				hqlStr += " AND sdv.gender = ? ";
			}
			if (studentDetail.getMajorInfo() != null) {
				hqlStr += " AND sdv.majorInfo = ? ";
			}
			if (studentDetail.getGrade() != null) {
				hqlStr += " AND sdv.grade = ? ";
			}
			if (studentDetail.getClassNo() != null) {
				hqlStr += " AND sdv.classNo = ? ";
			}
		}
		hqlStr += " AND siv.states <> ?  ORDER BY siv.stuNo DESC";
		final String hql = hqlStr;
		list = this.getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query query = session.createQuery(hql);
				int index = -1;
				if (stuNo != null) {
					query.setString(++index, stuNo);
				}
				if (stuName != null) {
					query.setString(++index, stuName);
				}
				if (states != null) {
					query.setInteger(++index, states);
				}
				if(studentDetail != null){
					if (studentDetail.getGender() != null) {
						query.setString(++index, studentDetail.getGender());
					}
					if (studentDetail.getMajorInfo() != null) {
						query.setEntity(++index, studentDetail.getMajorInfo());
					}
					if (studentDetail.getGrade() != null) {
						query.setInteger(++index, studentDetail.getGrade());
					}
					if (studentDetail.getClassNo() != null) {
						query.setInteger(++index, studentDetail.getClassNo());
					}
				}
				query.setInteger(++index, IDEDStates.DELETE);
				query.setFirstResult(rows * (pageNo - 1));
				query.setMaxResults(rows);
				list = query.list();
				return list;
			}
		});
		return list;
	}

	@SuppressWarnings("unchecked")
	@Override
	public Integer getTotalPageNumberAboveStates(final String stuNo, final String stuName,
			final Integer states, final StudentDetailValue studentDetail, Integer rows) {
		int totalPageNumber = 0;
		String hqlStr = "SELECT siv FROM StudentInfoValue siv, StudentDetailValue sdv WHERE 1=1 AND siv.stuDetail.id = sdv.id ";
		if (stuNo != null) {
			hqlStr += " AND siv.stuNo = ? ";
		}
		if (stuName != null) {
			hqlStr += " AND siv.stuName = ? ";
		}
		if (states != null) {
			hqlStr += " AND siv.states >= ? ";
		}
		if(studentDetail != null){
			if (studentDetail.getGender() != null) {
				hqlStr += " AND sdv.gender = ? ";
			}
			if (studentDetail.getMajorInfo() != null) {
				hqlStr += " AND sdv.majorInfo = ? ";
			}
			if (studentDetail.getGrade() != null) {
				hqlStr += " AND sdv.grade = ? ";
			}
			if (studentDetail.getClassNo() != null) {
				hqlStr += " AND sdv.classNo = ? ";
			}
		}
		hqlStr += " AND siv.states <> ?  ORDER BY siv.stuNo";
		final String hql = hqlStr;
		list = this.getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query query = session.createQuery(hql);
				int index = -1;
				if (stuNo != null) {
					query.setString(++index, stuNo);
				}
				if (stuName != null) {
					query.setString(++index, stuName);
				}
				if (states != null) {
					query.setInteger(++index, states);
				}
				if(studentDetail != null){
					if (studentDetail.getGender() != null) {
						query.setString(++index, studentDetail.getGender());
					}
					if (studentDetail.getMajorInfo() != null) {
						query.setEntity(++index, studentDetail.getMajorInfo());
					}
					if (studentDetail.getGrade() != null) {
						query.setInteger(++index, studentDetail.getGrade());
					}
					if (studentDetail.getClassNo() != null) {
						query.setInteger(++index, studentDetail.getClassNo());
					}
				}
				query.setInteger(++index, IDEDStates.DELETE);
				list = query.list();
				return list;
			}
		});
		totalNumber = list.size();
		if (totalNumber <= rows) {
			totalPageNumber = 1;
		} else {
			if (totalNumber % rows == 0) {
				totalPageNumber = totalNumber / rows;
			} else {
				totalPageNumber = totalNumber / rows + 1;
			}
		}
		return totalPageNumber;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<StudentInfoValue> queryStudentInfo(
			final StudentDetailValue studentDetail) throws Exception {
		String hqlStr = "SELECT siv FROM StudentInfoValue siv, StudentDetailValue sdv WHERE 1=1 AND siv.stuDetail.id = sdv.id ";
		if(studentDetail != null){
			if (studentDetail.getGender() != null) {
				hqlStr += " AND sdv.gender = ? ";
			}
			if (studentDetail.getMajorInfo() != null) {
				hqlStr += " AND sdv.majorInfo = ? ";
			}
			if (studentDetail.getGrade() != null) {
				hqlStr += " AND sdv.grade = ? ";
			}
		}
		hqlStr += " AND siv.states = ?";
		final String hql = hqlStr;
		list = this.getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query query = session.createQuery(hql);
				int index = -1;
				if(studentDetail != null){
					if (studentDetail.getGender() != null) {
						query.setString(++index, studentDetail.getGender());
					}
					if (studentDetail.getMajorInfo() != null) {
						query.setEntity(++index, studentDetail.getMajorInfo());
					}
					if (studentDetail.getGrade() != null) {
						query.setInteger(++index, studentDetail.getGrade());
					}
				}
				query.setInteger(++index, IStudentStates.CREATE);
				list = query.list();
				return list;
			}
		});
		return list;
	}



}
